﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class DAL_Category : clsConnection
{
    DataTable dt = new DataTable();
    clsQueryList clsquery = new clsQueryList();
    public DataTable DAL_Category_getData()
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Category WHERE Status = 1 ORDER BY Type");
        }
        catch
        {
            
        }
        return dt;
    }
    public DataTable DAL_Category_getAllData()
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Category");
        }
        catch
        {

        }
        return dt;
    }
    public DataTable DAL_Category_getMaxOrder()
    {
        try
        {
            dt = ExcuteQuery("SELECT max(Type) as 'type' FROM Category");
        }
        catch
        {

        }
        return dt;
    }
    public DataTable DAL_Category_getMinCgid()
    {
        try
        {
            dt = ExcuteQuery("SELECT min(Cgid) as 'cgid' FROM Category");
        }
        catch
        {

        }
        return dt;
    }
    public DataTable DAL_Category_getById(int Id)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Category WHERE Status = 1 AND Cgid = "+ Id +" ORDER BY Type");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Category_getAllById(int Id)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Category WHERE Cgid = " + Id + " ORDER BY Type");
        }
        catch
        {
        }
        return dt;
    }
    public DTO_Category DAL_LoadCategory(int cgid)
    {
        DTO_Category dtoC = new DTO_Category();
        DataTable dt1 = new DataTable();
        //SqlConnection db = new SqlConnection();
        string sql = "SELECT * FROM Category WHERE Status = 1 AND Cgid = @cgid";
        AddParameter(new SqlParameter("@cgid", cgid));
        try
        {
            dt1 = ExcuteQuery(sql);
            if (dt1.Rows.Count > 0)
            {
                dtoC.Cgid = (int)dt1.Rows[0]["Cgid"];
                dtoC.Name = dt1.Rows[0]["Name"].ToString();
                dtoC.Type = (int)dt1.Rows[0]["Type"];
                dtoC.Status = (int)dt1.Rows[0]["Status"];
            }
        }
        catch (Exception ex)
        {
            
            throw new Exception(ex.Message);
        }
        return dtoC;
    }
    public Boolean DAL_Category_addtodel(int cgid)
    {
        try
        {
            string sql = "delete from Category where Cgid=" + cgid;
            clsquery.addSQL(sql);
        }
        catch
        {
            return false;
        }
        return true;
    }
    public Boolean DAL_Category_del()
    {
        try
        {
            clsquery.Execute();
        }
        catch
        {
            return false;
        }
        return true;
    }
    public Boolean DAL_Category_Update(DTO_Category obj)
    {
        try
        {
            string sql = "UPDATE Category SET Name = @name, Type = @type, Status = @status WHERE Cgid = @cgid";
            AddParameter(new SqlParameter("@name", obj.Name));
            AddParameter(new SqlParameter("@order", obj.Type));
            AddParameter(new SqlParameter("@status", obj.Status));
            AddParameter(new SqlParameter("@cgid", obj.Cgid));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public Boolean DAL_Category_Insert(DTO_Category obj)
    {
        try
        {
            string sql = "INSERT INTO Category (Name, Type, Status) VALUES (@name, @order, @status)";
            AddParameter(new SqlParameter("@name", obj.Name));
            AddParameter(new SqlParameter("@order", obj.Type));
            AddParameter(new SqlParameter("@status", obj.Status));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
}